package jp.ac.u_tokyo.dch.dao.data;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.DataDetail;
import jp.ac.u_tokyo.dch.model.db.DataItem;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;
/**
* @version 1.1
*/
public class DataDetailDao {

	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 * @param DBコネクションオブジェクト
	 */
	public DataDetailDao(DBConnection dbconn){
		this.dbconn = dbconn;
	}


	/**
	 * データ詳細を挿入する
	 * @param list データ詳細リスト
	 * @param dataId データID
	 * @return 挿入件数情報
	 * @throws DBException 例外
	 */
	public int[] insertAll(List<DataDetail> list, int dataId)  throws DBException {

		if (list == null) {
			return null;
		}

		CustomPreparedStatement ps = null;
		int result[] = null;
		try{
			String query = "INSERT INTO data_detail " +
					"(dataid, " +
					"fieldkey," +
					"datatype)" +
					" VALUES (?,?,?)";

			ps = dbconn.prepareStatement(query);

			for (DataDetail detail : list) {

				int index = 1;
				ps.setInt(index++, dataId);
				ps.setString(index++, detail.getFieldKey());
				ps.setString(index++, detail.getDataType());
				ps.addBatch();

				List<DataItem> dataItemList = detail.getList();

				if (dataItemList != null) {
					insertItems(dataItemList, dataId, detail.getFieldKey());
				}
			}

			result = ps.executeBatch();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;

	}

	/**
	 * 削除する
	 * @param dataId データID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(int dataId)  throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{

			String query = "DELETE FROM data_detail WHERE dataid = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(1, dataId);
			result = ps.executeUpdate();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;

	}

	/**
	 * 更新する
	 * @param list 更新リスト
	 * @param dataId データID
	 * @return 更新件数情報
	 * @throws DBException 例外
	 */
	public int[] updateAll(List<DataDetail> list, int dataId)  throws DBException {

		if (list == null) {
			return null;
		}

		CustomPreparedStatement ps = null;
		int result[] = null;
		try{

			String query = "UPDATE data_detail SET datatype = ? WHERE dataid = ? and fieldkey = ?";


			ps = dbconn.prepareStatement(query);

			for (DataDetail detail : list) {

				int index = 1;
				ps.setString(index++, detail.getDataType());
				ps.setInt(index++, dataId);
				ps.setString(index++, detail.getFieldKey());
				ps.addBatch();

				List<DataItem> dataItemList = detail.getList();

				if (dataItemList != null) {
					updateItems(dataItemList, dataId, detail.getFieldKey());
				}
			}

			result = ps.executeBatch();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;

	}

	/**
	 * データアイテム情報を挿入する
	 * @param list データアイテム情報
	 * @param dataId データID
	 * @param fieldKey 項目キー
	 * @return 挿入件数情報
	 * @throws DBException 例外
	 */
	public int[] insertItems(List<DataItem> list, int dataId, String fieldKey)  throws DBException {

		CustomPreparedStatement ps = null;
		int result[] = null;
		try{
			String query = "INSERT INTO data_items " +
					"(dataid, " +
					"fieldkey," +
					"sort," +
					"data," +
					"textareadata)" +
					" VALUES (?,?,?,?,?)";

			ps = dbconn.prepareStatement(query);

			for (DataItem item : list) {

				int index = 1;
				ps.setInt(index++, dataId);
				ps.setString(index++, fieldKey);
				ps.setString(index++, item.getSort());
				ps.setString(index++, item.getDbData());
				ps.setString(index++, item.getDbAreaData());
				ps.addBatch();
			}

			result = ps.executeBatch();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * データアイテム情報を削除する
	 * @param dataId データID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int deleteItems(int dataId) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{

			String query = "DELETE FROM data_items WHERE dataid = ?";
			ps = dbconn.prepareStatement(query);
			ps.setInt(1, dataId);
			result = ps.executeUpdate();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;

	}

	/**
	 * データアイテム情報を更新する
	 * @param list データアイテム情報リスト
	 * @param dataId データID
	 * @param fieldKey 項目キー
	 * @return 更新件数情報
	 * @throws DBException 例外
	 */
	public int[] updateItems(List<DataItem> list, int dataId, String fieldKey)  throws DBException {

		CustomPreparedStatement ps = null;
		int result[] = null;
		try{

			String query = "UPDATE data_items SET sort = ?,data = ?,textareadata=? WHERE dataid = ? and fieldkey =?";

			ps = dbconn.prepareStatement(query);

			for (DataItem item : list) {

				int index = 1;
				ps.setString(index++, item.getSort());
				ps.setString(index++, item.getDbData());
				ps.setString(index++, item.getDbAreaData());
				ps.setInt(index++, dataId);
				ps.setString(index++, fieldKey);
				ps.addBatch();
			}

			result = ps.executeBatch();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}
		return result;
	}

	/**
	 * データ情報を取得する
	 * @param dataId データID
	 * @param customSign カスタムサイン（０：カスタムではない、１：カスタム）
	 * @return データ詳細リスト
	 * @throws DBException 例外
	 */
	public List<DataDetail> selectById(String dataId, String customSign)  throws DBException {

		List<DataDetail> detailList = new ArrayList<DataDetail>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT * FROM ");
		sqlQuery.append("data_detail ");
		sqlQuery.append("INNER JOIN data ");
		sqlQuery.append("INNER JOIN data_schema_detail ");

		sqlQuery.append("on data.id = data_detail.dataid ");
		sqlQuery.append("and data_schema_detail.schemaid = data.schemaid ");
		sqlQuery.append("and data_schema_detail.fieldkey = data_detail.fieldkey ");
		sqlQuery.append("WHERE data_schema_detail.customsign = ? and data.id= ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, customSign);
			ps.setString(2, dataId);
			rs = ps.executeQuery();

			while (rs.next()) {

				DataDetail detail = new DataDetail();

				detail.setDataId(rs.getInt("dataid"));
				detail.setFieldKey(rs.getString("fieldkey"));
				detail.setDataType(rs.getString("datatype"));

				setDetailDataItems(detail);

				detailList.add(detail);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return detailList;

	}

	/**
	 * データ情報を取得する
	 * @param dataId データID
	 * @param customSign カスタムサイン（０：カスタムではない、１：カスタム）
	 * @return データ詳細リスト
	 * @throws DBException 例外
	 */
	public Map<String, DataDetail> selectByIdForMap(String dataId, String customSign)  throws DBException {

		Map<String, DataDetail> detailList = new HashMap<String, DataDetail>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT * FROM ");
		sqlQuery.append("data_detail ");
		sqlQuery.append("INNER JOIN data ");
		sqlQuery.append("INNER JOIN data_schema_detail ");

		sqlQuery.append("on data.id = data_detail.dataid ");
		sqlQuery.append("and data_schema_detail.schemaid = data.schemaid ");
		sqlQuery.append("and data_schema_detail.fieldkey = data_detail.fieldkey ");
		sqlQuery.append("WHERE data_schema_detail.customsign = ? and data.id= ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, customSign);
			ps.setString(2, dataId);
			rs = ps.executeQuery();

			while (rs.next()) {

				DataDetail detail = new DataDetail();

				detail.setDataId(rs.getInt("dataid"));
				detail.setFieldKey(rs.getString("fieldkey"));
				detail.setDataType(rs.getString("datatype"));

				setDetailDataItems(detail);

				detailList.put(detail.getFieldKey(), detail);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return detailList;

	}

	/**
	 * データ情報を取得する
	 * @param dataId データID
	 * @return データ詳細リスト
	 * @throws DBException 例外
	 */
	public Map<String, DataDetail> selectL3ByIdForMap(String lv1DataId, String lv2DataId, String lv3DataId)  throws DBException {

		Map<String, DataDetail> detailList = new HashMap<String, DataDetail>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT * FROM ");
		sqlQuery.append("data_detail ");
		sqlQuery.append("INNER JOIN data ");
		sqlQuery.append("INNER JOIN data_schema_detail ");

		sqlQuery.append("on data.id = data_detail.dataid ");
		sqlQuery.append("and data_schema_detail.schemaid = data.schemaid ");
		sqlQuery.append("and data_schema_detail.fieldkey = data_detail.fieldkey ");
		sqlQuery.append("WHERE data.id IN (?, ?, ?)");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, lv1DataId);
			ps.setString(2, lv2DataId);
			ps.setString(3, lv3DataId);
			rs = ps.executeQuery();

			while (rs.next()) {

				DataDetail detail = new DataDetail();

				detail.setDataId(rs.getInt("dataid"));
				detail.setFieldKey(rs.getString("fieldkey"));
				detail.setDataType(rs.getString("datatype"));

				setDetailDataItems(detail);

				detailList.put(detail.getFieldKey(), detail);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return detailList;

	}


	/**
	 * データ情報を取得する
	 * @param dataId データID
	 * @return データ詳細リスト
	 * @throws DBException 例外
	 */
	public Map<String, DataDetail> selectByIdForMap(String dataId)  throws DBException {

		Map<String, DataDetail> detailList = new HashMap<String, DataDetail>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT * FROM ");
		sqlQuery.append("data_detail ");
		sqlQuery.append("INNER JOIN data ");
		sqlQuery.append("INNER JOIN data_schema_detail ");

		sqlQuery.append("on data.id = data_detail.dataid ");
		sqlQuery.append("and data_schema_detail.schemaid = data.schemaid ");
		sqlQuery.append("and data_schema_detail.fieldkey = data_detail.fieldkey ");
		sqlQuery.append("WHERE data.id= ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, dataId);
			rs = ps.executeQuery();

			while (rs.next()) {

				DataDetail detail = new DataDetail();

				detail.setDataId(rs.getInt("dataid"));
				detail.setFieldKey(rs.getString("fieldkey"));
				detail.setDataType(rs.getString("datatype"));

				setDetailDataItems(detail);

				detailList.put(detail.getFieldKey(), detail);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return detailList;

	}


	/**
	 * 項目キーでデータを検索する
	 * @param field 項目キー
	 * @return データ詳細リスト
	 * @throws DBException 例外
	 */
	public List<DataDetail> selectGroupByField(String field) throws DBException {

		List<DataDetail> detailList = new ArrayList<DataDetail>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT * FROM total_data_items WHERE fieldkey = ? group by datacontent ");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, field);
			rs = ps.executeQuery();

			while (rs.next()) {

				DataDetail detail = new DataDetail();

				detail.setDbData(rs.getString("datacontent"));
				detail.setFieldKey(rs.getString("fieldkey"));

				detailList.add(detail);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return detailList;

	}

	/**
	 * データ情報を取得する
	 * @param dataId データID
	 * @return データ詳細リスト
	 * @throws DBException 例外
	 */
	public List<DataDetail> selectByIdForDownLoad(String dataId)  throws DBException {

		List<DataDetail> detailList = new ArrayList<DataDetail>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select * FROM field_master LEFT JOIN ");
		sqlQuery.append("data_detail on field_master.fieldkey = data_detail.fieldkey where data_detail.dataid = ? ");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, dataId);
			rs = ps.executeQuery();

			while (rs.next()) {

				DataDetail detail = new DataDetail();

				detail.setDataId(rs.getInt("dataid"));
				detail.setFieldKey(rs.getString("fieldkey"));
				detail.setDataType(rs.getString("datatype"));
				detail.setFieldName(rs.getString("itemname"));
				setDetailDataItems(detail);

				detailList.add(detail);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return detailList;

	}

	/**
	 * データ詳細アイテムをセットする
	 * @param detail データ詳細
	 * @throws DBException 例外
	 */
	private void setDetailDataItems(DataDetail detail) throws DBException {

		List<DataItem> list = new ArrayList<DataItem>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select * from data_items where dataid = ? and fieldkey = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setInt(1, detail.getDataId());
			ps.setString(2, detail.getFieldKey());
			rs = ps.executeQuery();

			while (rs.next()) {

				DataItem item = new DataItem();
				item.setSort(rs.getString("sort"));
				item.setDbData(rs.getString("data"));
				item.setDbAreaData(rs.getString("textareadata"));
				list.add(item);
			}

			detail.setList(list);

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
	}

}
